import datetime
import json
import os
import re

import cx_Oracle

"""在存储中匹配字符串"""

user = "pigpos"
passwd = "P1g#2023pos"
listener = 'CTCNZQF.cpchina.cn/CTCNZQF'
conn = cx_Oracle.connect(user, passwd, listener)
cursor = conn.cursor()


def select(sql) -> json:
    rows = []
    cursor.execute(sql)
    result = cursor.fetchall()
    col_name = cursor.description
    for row in result:
        d = {}
        for col in range(len(col_name)):
            key = col_name[col][0]
            value = row[col]
            if isinstance(value, datetime.datetime):
                value = value.strftime('%Y-%m-%d %H:%M:%S')
            d[key] = value
        rows.append(d)
    js = json.dumps(rows, sort_keys=True, ensure_ascii=False, separators=(',', ':'))
    return js


# 关闭连接，释放资源
def disconnect():
    cursor.close()
    conn.close()
    print("关闭数据库连接")


def write2txt(cur_procedure, rs):
    # print('写文件')
    path = "C:/Users/yangjianzhang/Desktop/procedure"
    if not os.path.exists(path):
        os.mkdir(path)
    file = path + "/" + cur_procedure + ".txt"
    with open(file, mode='w', encoding='utf-8') as f:
        j = json.loads(rs)
        for text in j:
            # print(text['TEXT'])
            f.write(text['TEXT'])


def find(key, array):
    # 查询当前存储是否包括
    cur_procedure = array[0]['NAME']
    sql = " SELECT text FROM user_source WHERE NAME = '" + cur_procedure + "' ORDER BY line"
    # 查询依赖存储是否包括
    rs = select(sql)
    # 将存储写到文件
    write2txt(cur_procedure, rs)
    # print(type(rs))
    a = 0
    if key.upper() in rs.upper():
        a = 1
    if a == 1:
        print('存储:%s,是否包含:%d' % (cur_procedure, a))
    for t in array:
        ref_name = t['REFERENCED_NAME']
        type_ = t['REFERENCED_TYPE']
        if type_ == 'PROCEDURE':
            dep = t['dependencies']
            find(key, dep)


if __name__ == '__main__':
    # 查询报错中文描述
    """ SELECT  *   FROM MAS_ERROR_MESSAGE where  error_id = 'FRCHK074'"""
    print('开始查询')
    t = 'FR_SW_JOB_COST'
    jsonFilePath = "C:/Users/yangjianzhang/Desktop/" + t + ".json"

    with open(jsonFilePath) as f:
        j = json.load(f)
    # print(j)
    key = "fr_sw_cos_finisher_header"
    print('key:%s' % key)
    find(key, j)
